﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace Housing
{
    public class ApplyHistory
    {
        private int _apply_id;
        private int _apply_noid;
        private string _apply_name;
        private string _apply_address;
        private string _apply_phone;
        private string _apply_time;
        private short _apply_checkstate;
        private short _apply_passed;
        private string _apply_street;
        private float _apply_yearincome;
        private int _apply_peoplenum;
        private float _apply_avgincome;
        private float _apply_totlearea;
        private float _apply_avgarea;
        private float _apply_totlepropertity;
        private string _apply_paybycard;
        private string _apply_docname;
        private string _apply_docnum;
        private string _apply_docdate;
        private string _apply_longid;
        private short _apply_updchecked;
        // 修改
        public string Marryed;
        public string Workunit;
        public string Idcard;
        public string Income;
        public string EditYearMonth;
        public string EditNote;

        public int apply_id
        {
            get { return _apply_id; }
        }
        public int apply_noid
        {
            get { return _apply_noid; }
            set { _apply_noid = value; }
        }
        public string apply_name
        {
            get { return _apply_name; }
            set { _apply_name = value; }
        }
        public string apply_address
        {
            get { return _apply_address; }
            set { _apply_address = value; }
        }
        public string apply_phone
        {
            get { return _apply_phone; }
            set { _apply_phone = value; }
        }
        public string apply_time
        {
            get { return _apply_time; }
            set { _apply_time = value; }
        }
        public short apply_checkstate
        {
            get { return _apply_checkstate; }
            set { _apply_checkstate = value; }
        }
        public short apply_passed
        {
            get { return _apply_passed; }
            set { _apply_passed = value; }
        }
        public string apply_street
        {
            get { return _apply_street; }
            set { _apply_street = value; }
        }
        public float apply_yearincome
        {
            get { return _apply_yearincome; }
            set { _apply_yearincome = value; }
        }
        public int apply_peoplenum
        {
            get { return _apply_peoplenum; }
            set { _apply_peoplenum = value; }
        }
        public float apply_avgincome
        {
            get { return _apply_avgincome; }
            set { _apply_avgincome = value; }
        }
        public float apply_totlearea
        {
            get { return _apply_totlearea; }
            set { _apply_totlearea = value; }
        }
        public float apply_avgarea
        {
            get { return _apply_avgarea; }
            set { _apply_avgarea = value; }
        }
        public float apply_totlepropertity
        {
            get { return _apply_totlepropertity; }
            set { _apply_totlepropertity = value; }
        }
        public string apply_paybycard
        {
            get { return _apply_paybycard; }
            set { _apply_paybycard = value; }
        }
        public string apply_docname
        {
            get { return _apply_docname; }
            set { _apply_docname = value; }
        }
        public string apply_docnum
        {
            get { return _apply_docnum; }
            set { _apply_docnum = value; }
        }
        public string apply_docdate
        {
            get { return _apply_docdate; }
            set { _apply_docdate = value; }
        }
        public string apply_longid
        {
            get { return _apply_longid; }
            set { _apply_longid = value; }
        }
        public short apply_updchecked
        {
            get { return _apply_updchecked; }
            set { _apply_updchecked = value; }
        }



        public ApplyHistory()
        {
        }

        public ApplyHistory(string applyid)
        {
            GetApplyById(applyid);
        }

        public DataSet GetAllApply()
        {
            string query = "SELECT * FROM Apply";

            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString,
                CommandType.Text, query);

            return ds;
        }
        /// <summary>
        /// /////////////////////////////////////
        /// </summary>
        /// <param name="id"></param>
        public static void checkHistory(string id)
        {

            string query = "UPDATE [ApplyHistory] SET [ischecked]=1 where [apply_id]=" + id;
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,CommandType.Text, query);
            
            
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@ID", Convert.ToInt32(id));
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, "updateApplyByHistoryData", param);    


            
            //query = "UPDATE [Apply] SET [SubsidyMoney]=(select SubsidyMoney from ApplyHistory where  [apply_id]=" + id + ")    where [apply_noid]=(select top 1 apply_noid from ApplyHistory where [apply_id]=" + id + ") ";
            //SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query);
        }
        public static string GetHTMLByID(string id)
        {
            string cmdtext = "SELECT top 1 HTML FROM ApplyHistory WHERE apply_id=" + id;
            string HTML = Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, cmdtext));
            return HTML;
        }

        public void GetApplyById(string applyid)
        {
            string query = "SELECT * FROM Apply,Applicant where Apply.apply_noid=Applicant.app_noid  and Applicant.app_title='申请人' and  Apply.apply_id=" + applyid;

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString,
                CommandType.Text, query);

            if (dr.Read())
            {
                _apply_id = (int)dr[0];
                _apply_noid = (int)dr[1];
                _apply_name = Utils.GetString(dr[2]);
                _apply_address = Utils.GetString(dr[3]);
                _apply_phone = Utils.GetString(dr[4]);
                _apply_time = Utils.GetString(dr[6]);
                _apply_checkstate = (short)Utils.GetNullableInt(dr[5]);
                _apply_passed = (short)Utils.GetNullableInt(dr[7]);
                _apply_street = Utils.GetString(dr[8]);
                _apply_yearincome = Utils.GetNullableFloat(dr[9]);
                _apply_peoplenum = Utils.GetNullableInt(dr[10]);
                _apply_avgincome = Utils.GetNullableFloat(dr[11]);
                _apply_totlearea = Utils.GetNullableFloat(dr[12]);
                _apply_avgarea = Utils.GetNullableFloat(dr[13]);
                _apply_totlepropertity = Utils.GetNullableFloat(dr[14]);
                _apply_paybycard = Utils.GetString(dr[15]);
                _apply_docname = Utils.GetString(dr[16]);
                _apply_docnum = Utils.GetString(dr[17]);
                _apply_docdate = Utils.GetString(dr[18]);
                _apply_longid = Utils.GetString(dr[19]);
                _apply_updchecked = (short)Utils.GetNullableInt(dr[20]);

                //修改
                Marryed = Utils.GetString(dr["app_marryed"]);
                Workunit = Utils.GetString(dr["app_workunit"]);
                Idcard = Utils.GetString(dr["app_idcard"]);
                Income = Utils.GetString(dr["app_income"]);
            }

            dr.Close();
        }

        public int DeleteAApplyByApplyId(int applyId)
        {
            string query = "DELETE FROM Apply WHERE apply_id="+applyId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
                CommandType.Text, query);
        }

        public int UpdateAApplyByApplyId(Apply apply)
        {
            string query = "UPDATE Apply SET apply_noid=" + apply.apply_noid + ",apply_name='" + apply.apply_name + "',"                
                           + "apply_address='" + apply.apply_address + "',apply_phone='" + apply.apply_phone + "',"
                           + "apply_time='" + apply.apply_time + "',apply_checkstate=" + apply.apply_checkstate + ","
                           + "apply_passed=" + apply.apply_passed + ",apply_street='" + apply.apply_street + "',"
                           + "apply_yearincome=" + apply.apply_yearincome + ",apply_peoplenum=" + apply.apply_peoplenum + ","
                           + "apply_avgincome=" + apply.apply_avgincome + ",apply_totlearea=" + apply.apply_totlearea + ","
                           + "apply_avgarea=" + apply.apply_avgarea + ",apply_totleproperty=" + apply.apply_totlepropertity + ","
                           + "apply_paybycard='" + apply.apply_paybycard + "',apply_docname='" + apply.apply_docname + "',"
                           + "apply_docnum='" + apply.apply_docnum +"',apply_docdate='"+apply.apply_docdate + "',"
                           + "apply_longid='" + apply.apply_longid + "',apply_updchecked=" + apply.apply_updchecked
                           + " WHERE apply_id=" + apply.apply_id;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
                CommandType.Text, query);
        }

        public int UpdateApplyHistoryByApplyId(Apply apply)
        {
            string query = "UPDATE ApplyHistory SET apply_noid=" + apply.apply_noid + ",apply_name='" + apply.apply_name + "',"
                           + "apply_address='" + apply.apply_address + "',apply_phone='" + apply.apply_phone + "',"
                           + "apply_time='" + apply.apply_time + "',apply_checkstate=" + apply.apply_checkstate + ","
                           + "apply_passed=" + apply.apply_passed + ",apply_street='" + apply.apply_street + "',"
                           + "apply_yearincome=" + apply.apply_yearincome + ",apply_peoplenum=" + apply.apply_peoplenum + ","
                           + "apply_avgincome=" + apply.apply_avgincome + ",apply_totlearea=" + apply.apply_totlearea + ","
                           + "apply_avgarea=" + apply.apply_avgarea + ",apply_totleproperty=" + apply.apply_totlepropertity + ","
                           + "apply_paybycard='" + apply.apply_paybycard + "',apply_docname='" + apply.apply_docname + "',"
                           + "apply_docnum='" + apply.apply_docnum + "',apply_docdate='" + apply.apply_docdate + "',"
                           + "apply_longid='" + apply.apply_longid + "',apply_updchecked=" + apply.apply_updchecked
                           + " WHERE apply_id=" + apply.apply_id;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
                CommandType.Text, query);
        }

        public static int UpdateApply_passedByApply_noid(string apply_id, string passedNum)
        {
            string query = "UPDATE Apply SET apply_passed=" + passedNum + " where apply_id=" + apply_id;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
                CommandType.Text, query);
        }

        public static int Update_apply_checkstate_ByApply_id(string apply_id,string stateNum)
        {
            string query = "UPDATE Apply SET apply_checkstate=" + stateNum + " where apply_id=" + apply_id;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,
                CommandType.Text, query);
        }

        public int AddNewApply(Apply apply)
        {
            string query = "INSERT INTO Apply(apply_noid, apply_name, apply_address, apply_phone, apply_time, apply_checkstate,"
                           + "apply_passed, apply_street, apply_yearincome, apply_peoplenum, apply_avgincome, apply_totlearea,"
                           + "apply_avgarea, apply_totleproperty, apply_paybycard, apply_docname, apply_docnum, apply_docdate," 
                           + "apply_longid, apply_updchecked)"
                           + "VALUES(" + apply.apply_noid + ",'" + apply.apply_name + "','" + apply.apply_address + "','" + apply.apply_phone + "',"
                           + "'" + apply.apply_time + "'," + apply.apply_checkstate + "," + apply.apply_passed + ",'" + apply.apply_street + "',"
                           + apply.apply_yearincome + "," + apply.apply_peoplenum + "," + apply.apply_avgincome + "," + apply.apply_totlearea + ","
                           + apply.apply_avgarea + "," + apply.apply_totlepropertity + ",'" + apply.apply_paybycard + "','" + apply.apply_docname + "',"
                           + "'" + apply.apply_docnum + "'," + apply.apply_docdate + ",'" + apply.apply_longid + "'," + apply.apply_updchecked+") select @@identity";

            return Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, query));
        }
        public int AddNewApplyHistory(Apply apply)
        {
            string query = "INSERT INTO ApplyHistory (apply_noid, apply_name, apply_address, apply_phone, apply_checkstate,"
                           + "apply_passed, apply_street, apply_yearincome, apply_peoplenum, apply_avgincome, apply_totlearea,"
                           + "apply_avgarea, apply_totleproperty, apply_paybycard, apply_docname, apply_docnum, apply_docdate,"
                           + "apply_longid, apply_updchecked,EditYearMonth,EditNote)"
                           + "VALUES(" + apply.apply_noid + ",'" + apply.apply_name + "','" + apply.apply_address + "','" + apply.apply_phone + "',"
                           +  apply.apply_checkstate + "," + apply.apply_passed + ",'" + apply.apply_street + "',"
                           + apply.apply_yearincome + "," + apply.apply_peoplenum + "," + apply.apply_avgincome + "," + apply.apply_totlearea + ","
                           + apply.apply_avgarea + "," + apply.apply_totlepropertity + ",'" + apply.apply_paybycard + "','" + apply.apply_docname + "',"
                           + "'" + apply.apply_docnum + "'," + apply.apply_docdate + ",'" + apply.apply_longid + "'," + apply.apply_updchecked + "," + apply.EditYearMonth + ",'"+apply.EditNote+"') select @@identity";

            return Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, query));
        }


        public int GetUserTypeNum(string UserType)
        {
            if (UserType == "辖区房管所")
                return 3;
            else if (UserType == "街道办事处")
                return 1;
            else if (UserType == "民政部门")
                return 4;
            else if (UserType == "住房保障管理中心")
                return 5;
            else
                return 0;
        }

        public static DataSet GetApplyHistoryListOrderByUserTypeAndStreet(string street)
        {

            string cmdtext;
            if(street=="海曙")
                cmdtext = @" select [apply_noid] 
                      ,[apply_id]
                      ,[apply_name]
                      ,[apply_address]
                      ,[apply_phone]
                      ,[apply_checkstate]
                      ,[apply_time]
                      ,[apply_passed]
                      ,[apply_street]
                      ,[EditYearMonth]
                      ,[ischecked]
                ,addtime,edittime  from
                (
                select *,rn = row_number() over(partition by [apply_noid],[EditYearMonth] order by [apply_id])
                from [ApplyHistory]
                ) t 
                where rn = 1
                 and [ischecked]=0 ";
            else
                            cmdtext = @" select [apply_noid] 
                      ,[apply_id]
                      ,[apply_name]
                      ,[apply_address]
                      ,[apply_phone]
                      ,[apply_checkstate]
                      ,[apply_time]
                      ,[apply_passed]
                      ,[apply_street]
                      ,[EditYearMonth]
                      ,[ischecked]
                ,addtime,edittime  from
                (
                select *,rn = row_number() over(partition by [apply_noid],[EditYearMonth] order by [apply_id])
                from [ApplyHistory]
                ) t 
                where rn = 1
                and  [apply_street]='" + street + "' and [ischecked]=0 ";
            

            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
            return ds;
        }

        public static DataSet GetApplyHistory_checked(string street)
        {

            string cmdtext;
            if (street == "海曙")
                cmdtext = @" select [apply_noid] 
                      ,[apply_id]
                      ,[apply_name]
                      ,[apply_address]
                      ,[apply_phone]
                      ,[apply_checkstate]
                      ,[apply_time]
                      ,[apply_passed]
                      ,[apply_street]
                      ,[EditYearMonth]
                      ,[ischecked]
                ,addtime,edittime  from
                (
                select *,rn = row_number() over(partition by [apply_noid],[EditYearMonth] order by [apply_id])
                from [ApplyHistory]
                ) t 
                where rn = 1
                 and [ischecked]=1 ";
            else
                cmdtext = @" select [apply_noid] 
                      ,[apply_id]
                      ,[apply_name]
                      ,[apply_address]
                      ,[apply_phone]
                      ,[apply_checkstate]
                      ,[apply_time]
                      ,[apply_passed]
                      ,[apply_street]
                      ,[EditYearMonth]
                      ,[ischecked]
                ,addtime,edittime  from
                (
                select *,rn = row_number() over(partition by [apply_noid],[EditYearMonth] order by [apply_id])
                from [ApplyHistory]
                ) t 
                where rn = 1
                and  [apply_street]='" + street + "' and [ischecked]=1 ";

            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
            return ds;
        }

        public SqlDataReader GetApplyForCheckByNoid(string noid)
        {
            string cmdtext = "SELECT TOP 1 Apply.*,(select count(*) from Applicant where app_noid=Apply.apply_noid and app_ispro=1) as c FROM Apply WHERE apply_noid=" + noid;
            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
            return dr;
        }

        public bool SetCheckStateAddOne(string noid)
        {
            string cmdtext = "UPDATE Apply SET apply_checkstate=apply_checkstate+1 WHERE apply_noid=" + noid;
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, cmdtext));
            return succ;
        }

        public bool SetApplyUnPass(string noid)
        {
            string query = "UPDATE Apply SET apply_passed=0 WHERE apply_noid=" + noid;
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query));
            return succ;
        }

       

        public bool AddLongIdToApply(string noid, string longid)
        {
            string query = "UPDATE Apply SET apply_longid='"+longid+"' WHERE apply_noid='"+noid+"'";
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query));
            return succ;
        }

        public bool SetApplyPass(string noid,string longid)
        {
            //审核通过下月生效
            DateTime dt = DateTime.Now.AddMonths(1);
            string starttime= (dt.Year * 100 + dt.Month).ToString();
            SubsidyRecord subsidy = new SubsidyRecord();
            subsidy.GetNewestSubsidyRecordByNoid(noid);
            if(subsidy.Adj_stoptime!=null)
            {
                subsidy.Adj_starttime = starttime;
                subsidy.Adj_longid = longid;
                subsidy.UpdateASubsidyRecord(subsidy);
            }
            ReduceRecord reduce = new ReduceRecord();
            reduce.GetNewestReduceRecordBynoid(noid);
            if (reduce.Adj_stoptime != null)
            {
                reduce.Adj_starttime = starttime;
                reduce.Adj_longid = longid;
                reduce.UpdateAReduceRecord(reduce);
            }
            KindRecord kind = new KindRecord();
            kind.GetNewestKindRecordBynoid(noid);
            if (kind.Adj_stoptime != null)
            {
                kind.Adj_starttime = starttime;
                kind.Adj_longid = longid;
                kind.UpdateAKindRecord(kind);
            }
            string cmdtext = "UPDATE Apply SET apply_passed=3 WHERE apply_noid=" + noid;
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, cmdtext));
            return succ;
        }

        public DataSet SearchAllApply(string street)
        {
            string cmdtext = @"SELECT * FROM Apply AS A WHERE apply_street LIKE '%" + street + "%' AND apply_updchecked=1 AND "
                            +"apply_passed>=2 AND apply_id IN (SELECT MAX(apply_id) FROM Apply WHERE apply_updchecked=1 AND apply_noid=A.apply_noid)";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
            return ds;
        }

        public DataSet SearchApplyByTerms(string filedname, string content, string street)
        {
            string cmdtext;
            if (filedname == "apply_longid")
                cmdtext = "SELECT * FROM Apply AS A WHERE apply_street LIKE '%" + street + "%' AND apply_updchecked=1 AND apply_passed>=2 "
                        +"AND apply_id IN (SELECT MAX(apply_id) FROM Apply WHERE apply_updchecked=1 AND apply_noid=A.apply_noid) AND apply_longid LIKE '%" + content + "%'";
            else
                cmdtext = "SELECT * FROM Apply AS A WHERE apply_street LIKE '%" + street + "%' AND apply_updchecked=1 AND apply_passed>=2 "
                        +"AND apply_id IN (SELECT MAX(apply_id) FROM Apply WHERE apply_updchecked=1 AND apply_noid=A.apply_noid) AND " + filedname + " LIKE '%" + content + "%'";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, cmdtext);
            return ds;
        }

        public DataSet GetDaoQiThisMonth(string tablename,string street)
        {
            DateTime dt = DateTime.Now;
            int time = dt.Year * 100 + dt.Month;
            string query = "SELECT DISTINCT Apply.apply_longid,Apply.apply_name, Apply.apply_time,  Apply.apply_address,"+
                           "Apply.apply_street, Apply.apply_noid FROM Apply INNER JOIN "+tablename+" ON "+
                           ""+tablename+".adj_longid=Apply.apply_longid WHERE "+
                           "Apply.apply_passed=3 AND Apply.apply_updchecked=1 AND Apply.apply_street LIKE '%" + street + "%' AND " +
                           "" + tablename + ".adj_stoptime='"+time+"' AND " + tablename + ".adj_longid = Apply.apply_longid";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString,CommandType.Text,query);
            return ds;
        }

        //===================以下补发是逻辑==========================
        //申请补发
        public bool ApplyReissue(string longid,string money, int time, string remark)
        {
            int currenttime = DateTime.Now.Year * 100 + DateTime.Now.Month;
            string cmdtext = "SELECT COUNT(*) FROM Reissue WHERE longid='" + longid + "' AND reissuetime=" + currenttime + "";
            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, cmdtext));
            if (count <= 0)
            {
                string query = @"INSERT INTO Reissue(longid,money,reissuetime,remark,checked) VALUES
                           ('" + longid + "','" + money + "'," + time + ",'" + remark + "',0)";
                bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query));
                return succ;
            }
            else
                return false;
        }

        //获取补发申请信息
        public DataSet GetReissue(string street)
        {
            string query = "";
            if (street == "海曙")
                query = @"SELECT R.*,A.apply_name,A.apply_address,A.apply_time FROM Apply AS A , Reissue AS R WHERE R.longid=A.apply_longid 
                         AND R.checked=0";
            else
                query = @"SELECT R.*,A.apply_name,A.apply_address,A.apply_time FROM Apply AS A , Reissue AS R WHERE R.longid=A.apply_longid 
                         AND R.checked=0 AND A.apply_street='" + street + "'";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString,CommandType.Text,query);
            return ds;
        }

        //通过补发申请
        public bool SetApplyReissuePass(string id)
        {
            string query = "UPDATE Reissue SET checked=1 WHERE id=" + id;
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString,CommandType.Text,query));
            return succ;
        }

        //否决补发申请
        public bool SetApplyReissueUnpass(string id)
        {
            string query = "DELETE FROM Reissue WHERE id=" + id;
            bool succ = Convert.ToBoolean(SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, query));
            return succ;
        }
    }
}